package com.fanli.scheduler.utils;

import com.fanli.scheduler.bean.GeneralColumn;
import com.fanli.scheduler.bean.GeneralTable;
import com.fanli.scheduler.bean.JdbcObject;
import org.apache.log4j.Logger;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * Created by zcfrank1st on 8/11/15.
 */
public enum ConnectMan {
    INSTANCE;

    private static Logger logger = Logger.getLogger(ConnectMan.class);

    private static Properties config;
    static {
        logger.info("#################开始加载数据库连接#################");
        InputStream in = ConnectMan.class.getResourceAsStream("/jdbcConnections.properties");
        try {
            final Properties properties = new Properties();
            properties.load(in);
            config = loadDbConfig(properties);
            //每隔10分钟重新加载数据
            new Timer().schedule(new TimerTask() {
                @Override
                public void run() {
                    try {
                        config = loadDbConfig(properties);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            },1000*60*10,1000*60*10);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    static Properties loadDbConfig(Properties properties) throws SQLException, ClassNotFoundException {
        String driverClassName="com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://kettle.db.51fanli.it:3306/ctl_logdb?characterEncoding=UTF8&autoReconnect=true" ;
        String username = "kettle_etl";
        String password = "kettle_etl123";
        if(properties.containsKey("driverClassName")){
            driverClassName=properties.getProperty("driverClassName");
        }
        if(properties.containsKey("url")){
            url=properties.getProperty("url");
        }
        if(properties.containsKey("username")){
            username=properties.getProperty("username");
        }
        if(properties.containsKey("password")){
            password=properties.getProperty("password");
        }
        Connection con = null;
        PreparedStatement prepareStatement = null;
        ResultSet resultSet = null;

        try{
            Class.forName(driverClassName);
            con = DriverManager.getConnection(url , username , password ) ;

            prepareStatement = con.prepareStatement("select `connect_property`,`domain_name`,`port`,`username`,`password` from etl_db_config");
            resultSet = prepareStatement.executeQuery();
            Properties config = new Properties();
            int i=0;
            while (resultSet.next()){
                String connect_property = resultSet.getString("connect_property");
                String domain_name = resultSet.getString("domain_name");
                String port = resultSet.getString("port");
                String usernameRes = resultSet.getString("username");
                String passwordRes = resultSet.getString("password");

                config.setProperty(connect_property+".ip",domain_name);
                config.setProperty(connect_property+".port",port  ==null ? "" : port);
                config.setProperty(connect_property+".username",usernameRes  == null ? "" : usernameRes);
                config.setProperty(connect_property+".password",passwordRes == null ? "" : passwordRes);
                i++;
            }
            logger.info("#################加载数据库连接完成#################=="+i);
            return config;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(resultSet ==null){
                resultSet.close();
            }
            if(prepareStatement ==null){
                prepareStatement.close();
            }
            if(con ==null){
                con.close();
            }
        }

        return  null;
    }

    public GeneralTable getPrimaries(String conn,String db,String table) {
        GeneralTable generalTable = new GeneralTable();
        Connection connection = null;
        ResultSet rs = null;
        try {
            connection = getConnetion(conn);
            //rs = connection.getMetaData().getColumns(db, null, table, null);
            rs = connection.getMetaData().getPrimaryKeys(db, null, table);
            List<GeneralColumn> list = new ArrayList<GeneralColumn>();
            while (rs.next()) {
                GeneralColumn gc = new GeneralColumn();
                gc.setName(rs.getString("COLUMN_NAME"));
                list.add(gc);
            }
            generalTable.setColumns(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            assert rs != null;
            try {
                rs.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        generalTable.setName(table);
        return generalTable;
    }

    private JdbcObject getJdbcObject(String connectProperty) {
        String ip = config.getProperty(connectProperty +".ip");
        String port = config.getProperty(connectProperty + ".port");
        String username = config.getProperty(connectProperty + ".username");
        String password = config.getProperty(connectProperty + ".password");

        JdbcObject jdbcObject = new JdbcObject();
        jdbcObject.setIp(ip);
        jdbcObject.setPort(port);
        jdbcObject.setUsername(username);
        jdbcObject.setPassword(password);

        return jdbcObject;
    }

    private String getJdbcUri (String connectProperty, JdbcObject jdbcObject) throws ClassNotFoundException {
        if (connectProperty.contains("mysql")) {
            Class.forName("com.mysql.jdbc.Driver");
            return "jdbc:mysql://" + jdbcObject.getIp() + ":" + jdbcObject.getPort()
                    + "?useUnicode=true&characterEncoding=utf8";
        } else if (connectProperty.contains("sqlserver")) {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            return "jdbc:sqlserver://" + jdbcObject.getIp() + ":" + jdbcObject.getPort();
        } else {
            throw new RuntimeException("not support jdbc uri, current support mysql and sqlserver");
        }
    }

    public Connection getConnetion(String connectProperty) throws Exception {
        JdbcObject jdbcObject = getJdbcObject(connectProperty);
        String jdbcUri = getJdbcUri(connectProperty, jdbcObject);

        return DriverManager.getConnection(jdbcUri, jdbcObject.getUsername(), jdbcObject.getPassword());
    }

    public static void main(String[] args) {
        String connectProperty = "sqlserver80";
        String ip = config.getProperty(connectProperty +".ip");
        String port = config.getProperty(connectProperty + ".port");
        String username = config.getProperty(connectProperty + ".username");
        String password = config.getProperty(connectProperty + ".password");

        JdbcObject jdbcObject = new JdbcObject();
        jdbcObject.setIp(ip);
        jdbcObject.setPort(port);
        jdbcObject.setUsername(username);
        jdbcObject.setPassword(password);
        System.out.println(jdbcObject);

    }

    public List<String> getAllDatabases (String connectProperty) throws Exception {
        Connection con = getConnetion(connectProperty);
        DatabaseMetaData meta = con.getMetaData();
        ResultSet rs = meta.getCatalogs();

        List<String> databases = new ArrayList<String>();
        while (rs.next()) {
            databases.add(rs.getString("TABLE_CAT"));
        }
        rs.close();
        con.close();
        return databases;
    }

    public Boolean isTableExists(String connProp,String db,String table)  {
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = getConnetion(connProp);
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getTables(db, null, table, null);
            if (rs.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            assert rs != null;
            try {
                rs.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return false;
    }

    public List<String> getTablesInfo(String conn,String db,String table) {
        Connection connection = null;
        ResultSet rs = null;
        List<String> list = new ArrayList<String>();
        try {
            connection = getConnetion(conn);
            rs = connection.getMetaData().getTables(db, null, table, null);
            while (rs.next()) {
                list.add(rs.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            assert rs != null;
            try {
                if (rs != null) {
                    rs.close();
                }
                if (connection!=null) {
                    connection.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public GeneralTable getTableDetails(String conn,String db,String table) {
        GeneralTable gt = new GeneralTable();
        Connection connection = null;
        ResultSet rs = null;
        try {
            connection = getConnetion(conn);
            rs = connection.getMetaData().getColumns(db, null, table, null);
            List<GeneralColumn> list = new ArrayList<GeneralColumn>();
            while (rs.next()) {
                GeneralColumn gc = new GeneralColumn();
                gc.setName(rs.getString("COLUMN_NAME"));
                gc.setType(rs.getString("TYPE_NAME"));
                if (rs.getString("TYPE_NAME").equalsIgnoreCase("DECIMAL")) {
                    int precision = rs.getInt("COLUMN_SIZE");
                    int scale = rs.getInt("DECIMAL_DIGITS");
                    gc.setType("DECIMAL(" + precision + "," + scale + ")");
                }
                gc.setIndex(rs.getString("ORDINAL_POSITION"));
                gc.setComment(rs.getString("REMARKS"));
                list.add(gc);
            }
            gt.setColumns(list);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            assert rs != null;
            try {
                rs.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        gt.setName(table);
        return gt;

    }


    public int createTable(String conn,String db,String sql) throws Exception{
        int ret = -1;
        Connection connection = null;
        Statement stmt = null;
        try {
            JdbcObject jdbcObject = getJdbcObject(conn);
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://" + jdbcObject.getIp() + ":" + jdbcObject.getPort() + ";" + "DatabaseName=" + db;
            logger.info("jdbc sqlserver url is: " + url);
            connection = DriverManager.getConnection(url,jdbcObject.getUsername(),jdbcObject.getPassword());
            logger.info("The jdbc connection instance is: " + connection);
            stmt = connection.createStatement();
            ret = stmt.executeUpdate(sql);
            logger.info("execute create table return code is :" + ret);
        }catch (Exception e) {
            logger.error("create table failed,the error is :",e);
            throw e;
        }finally {
            try{
                if(stmt!=null)
                    stmt.close();
            }catch(SQLException se){
                se.printStackTrace();
            }// do nothing
            try{
                if(connection!=null)
                    connection.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }
        return ret;
    }


}
